﻿/********************************************************************************************************************
* Author:	Christos Polydorou																						*
* Email:	std04237@di.uoa.gr																						*
* Purpose:	This script is used in order to create the stored procedure "usp_user_deletecategory" that deletes a	*
*			category, all it's entities and attributes.																*
********************************************************************************************************************/

CREATE PROCEDURE [dbo].[usp_user_deletecategory]
	@username nvarchar(50),
	@catname nvarchar(50)
AS
	DECLARE @userid bigint
	DECLARE @catid bigint
	DECLARE @entityid bigint
	DECLARE entity_cursor CURSOR
	FOR
		SELECT Entities.ID
		FROM Entities
		WHERE Entities.Category = @catid

	SELECT @userid = ID												/* get the id of the owner */
	FROM Users
	where USers.Username = @username

	SELECT @catid = Categories.ID									/* get the id of the category */
	FROM Categories
	WHERE Categories.Name = @catname and Categories.Owner = @userid

	DELETE FROM [Category-User]										/* remove the between users and */
	WHERE [Category-User].CategoryID = @catid						/* the category */

	OPEN entity_cursor												/* delete all attributes from */
	FETCH NEXT FROM entity_cursor									/* all entities in category */
	INTO @entityid
	WHILE @@FETCH_STATUS = 0
		BEGIN
			DELETE FROM [Entity-Attribute]
			WHERE [Entity-Attribute].Entity = @entityid

			FETCH NEXT FROM entity_cursor
			INTO @entityid
		END
	CLOSE entity_cursor
	DEALLOCATE entity_cursor

	DELETE FROM Attributes											/* delete all attributes from category */
	WHERE Attributes.Category = @catid

	DELETE FROM Categories											/* delete the category */
	WHERE Categories.ID = @catid

RETURN 0